package com.ztc.util;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import com.ztc.bean.ProjectModel;
import com.ztc.bean.QuoteModel;
import com.ztc.bean.TD;
import com.ztc.bean.TR;

/**
 * @类名：ExcelQuoteUtil.java
 * @作者：李晓平
 * @时间：2018年7月16日 上午10:07:35
 * @描述：明细表引用工具类
 */
public class ExcelQuoteUtil {
//	private static NumberFormatUtil nf = new NumberFormatUtil("#,##0.00"); //保留两位小数

	/**
	 * 获取单元格的值
	 * @param excelStr
	 * @param row
	 * @param column
	 * @return
	 */
	public static String getExcelCell(String excelStr, String modelStr, int row, int column) {
		if(StringUtils.isBlank(excelStr)) {
			return "";
		}
		Document doc = Jsoup.parse(excelStr);
		Elements tableEle = doc.getElementsByTag("table");
		Elements trs = tableEle.get(0).getElementsByTag("tr");
		if(trs.size() < row) {
			return "";
		}
		//与模板对比，计算实际行数
		Document modelDoc = Jsoup.parse(modelStr);
		Elements modelTableEle = modelDoc.getElementsByTag("table");
		Elements modelTrs = modelTableEle.get(0).getElementsByTag("tr");
		row = row + trs.size() - modelTrs.size();
		
		Element tr = trs.get(row - 1);
		Elements tds = tr.getElementsByTag("td");
		int columnIndex = 0;
		for(Element td : tds) {
			if(columnIndex == (column - 1)) {
				Elements inputs = td.getElementsByTag("input");
				if(inputs == null || inputs.size() == 0) {
					return "";
				}
//				return nf.format(inputs.get(0).attr("value"));
				return inputs.get(0).attr("value");
			}
			if(StringUtils.isNotBlank(td.attr("colspan"))) {
				columnIndex+=Integer.parseInt(td.attr("colspan")) - 1;
			}
			columnIndex++;
		}
		return "";
	}
	
	/**
	 * 获取明细表值插入模板中，值为空行去掉
	 * @param excelStr
	 * @param modelStr
	 * @return
	 */
	public static String getExcelTable(String excelStr, String modelStr, String relation) {
		if(StringUtils.isBlank(excelStr) || StringUtils.isBlank(modelStr)) {
			return "";
		}
		Document excelDoc = Jsoup.parse(excelStr);
		Elements excelTable = excelDoc.getElementsByTag("table");
		Elements excelTrs = excelTable.get(0).getElementsByTag("tr");
		Document modelDoc = Jsoup.parse(modelStr);
		Elements modelTable = modelDoc.getElementsByTag("table");
		Elements modelTrs = modelTable.get(0).getElementsByTag("tr");
		int firstNum = 0;
		int secondNum = 0;
		for(int i = 0; i < modelTrs.size(); i++) {
			Element modelTr = modelTrs.get(i);
			Elements modelTds = modelTr.getElementsByTag("td");
			boolean flag = false;
			for(Element modelTd : modelTds) {
				String text = modelTd.text().trim();
				//引用规则
				if(text.startsWith("$") && text.endsWith("$")) {
					flag = true;
					String[] indexs = text.substring(1, text.length() - 1).split(",");
					int row = Integer.parseInt(indexs[0]);
					int column = Integer.parseInt(indexs[1]);
					Element td = excelTrs.get(row - 1).getElementsByTag("td").get(column - 1);
					Elements inputs = td.getElementsByTag("input");
					if(inputs != null && inputs.size() > 0) {
						String val = inputs.get(0).attr("value");
//						if(StringUtils.isBlank(val) || nf.format(val).equals("0.00") || nf.format(val).equals("0.00%")) {//值为空或0，删除该行
						if(StringUtils.isBlank(val) || val.equals("0.00") || val.equals("0.00%")) {//值为空或0，删除该行
							modelTr.remove();
						} else {
//							modelTd.text(nf.format(val));	
							modelTd.text(val);

							//基础法--分类汇总，编号处理
							if(relation.equals("00020003")) {
								if(StringUtils.isNotBlank(modelTds.get(1).text())) {
									modelTds.get(0).text(NumberUtil.numberToChinese(++firstNum));
									secondNum = 0;
								} else {
									modelTds.get(0).text((++secondNum) + "");
								}
								modelTds.get(1).remove();
							}
						}
					}
				}
			}
			//基础法--分类汇总，编号处理
			if(!flag && relation.equals("00020003")) {
				if(modelTds.size() > 1) {
					modelTds.get(1).remove();
				}
			}
		}
		return modelTable.outerHtml();
	}
	
	/**
	 * 获取明细表指定行列
	 * 值为空的行删掉
	 * @param excelStr
	 * @param rows
	 * @param columns
	 * @return
	 */
	public static String getExcelTable2(String excelStr, String modelStr, QuoteModel quote,
			ProjectModel project) {
		if(StringUtils.isBlank(excelStr)) {
			return "";
		}
		String rows = quote.getRow();
		String columns = quote.getColumn();
		String header = quote.getHeader();
		String notnull = quote.getNotnull();
		String relation = quote.getRelation();
		String keyword = quote.getKeyword();
		
		Date baseDate = project.getBaseDate();
		String baseDateStr = "";
		if(baseDate != null) {
			baseDateStr = new SimpleDateFormat("yyyy年MM月dd日").format(baseDate);
		}
		
		Document doc = Jsoup.parse(excelStr);
		Elements tableEle = doc.getElementsByTag("table");
		Elements trs = tableEle.get(0).getElementsByTag("tr");
		
		Document modelDoc = Jsoup.parse(modelStr);
		Elements modelTable = modelDoc.getElementsByTag("table");
		Elements modelTrs = modelTable.get(0).getElementsByTag("tr");
		
		String[] rowsArr = rows.split(",");
		String[] columnArr = columns.split(",");
		//要获取的行
		Set<Integer> holdRows = new LinkedHashSet<Integer>();
		for(String oneRows : rowsArr) {
			String[] rowArr = oneRows.split(":");
			if(rowArr.length == 1) {
				holdRows.add(Integer.parseInt(rowArr[0]));
			} else {
				//处理增加行情况
				int endRow = Integer.parseInt(rowArr[1]);
				if(trs.size() > modelTrs.size()) {
					endRow += trs.size() - modelTrs.size();
				}
				
				for(int i = Integer.parseInt(rowArr[0]); i <= endRow; i++) {
					holdRows.add(i);
				}
			}
		}
		//用于处理编号
		int rowNum = 0;//编号
		int firstNum = 0;//一级编号
		int secondNum = 0;//二级编号
		for(int i = 0; i < trs.size(); i++) {
			Element tr = trs.get(i);
			//不保留的行删掉
			if(!holdRows.contains(i + 1)) {
				tr.remove();
				continue;
			}
			//值为空的行删掉
			Elements tds = tr.getElementsByTag("td");
//			Iterator<Integer> it = holdRows.iterator();
//			int startRow = 0;
//			if(it.hasNext()) {
//				startRow = it.next() - 1;
//			}
//			if(StringUtils.isBlank(header) || (i + 1) > startRow + Integer.parseInt(header)) {
				if(StringUtils.isNotBlank(notnull)) {
					String[] notnullArr = notnull.split(",");
					boolean isTrEmpty = true;
					for(String oneNotnull : notnullArr) {
						int columnIndex = Integer.parseInt(oneNotnull);
						int tdIndex = 0;
						for(int j = 0; j < tds.size(); j++) {
							Element td = tds.get(j);
							if(td.text().equals("合计")) {//长期股权投资使用
								isTrEmpty = false;
								break;
							}
							if(tdIndex == (columnIndex - 1)) {
								Elements inputs = td.getElementsByTag("input");
								if(inputs != null && inputs.size() > 0) {
									String val = inputs.get(0).attr("value");
//									if(StringUtils.isNotBlank(val) && !nf.format(val).equals("0.00") && !nf.format(val).equals("0.00%")) {
									if(StringUtils.isNotBlank(val) && !val.equals("0.00") && !val.equals("0.00%")) {
										isTrEmpty = false;
										break;
									}
								} else if(td.hasText() && !td.text().equals("0.00") && !td.text().equals("0.00%")) {
									isTrEmpty = false;
									break;
								}
							}
							if(StringUtils.isNotBlank(td.attr("colspan"))) {
								tdIndex+=Integer.parseInt(td.attr("colspan")) - 1;
							}
							tdIndex++;
						}
						
						if(tdIndex < columnIndex || tds.size() < columnIndex) {
							isTrEmpty = false;
							break;
						}						
					}
					if(isTrEmpty) {
						tr.remove();
						if(relation.equals("00020037") && i == trs.size() - 4) {// 长期股权投资
							trs.get(i - 1).remove();
						}
						continue;
					}
//				}
			}
			
			boolean isEmpty = true; //判断单元格是不是空的，都为空删掉行
			for(int j = 0, tdIndex = 0; j < tds.size(); j++) {
				Element td = tds.get(j);
				
				boolean flag = false;
				for(String column : columnArr) {
					if(tdIndex == (Integer.parseInt(column) - 1)) {
						flag = true;
						break;
					}
				}
				if(!flag) {
					td.remove();
					tdIndex++;
					continue;
				} else {
					Elements inputs = td.getElementsByTag("input");
					if(inputs != null && inputs.size() > 0) { //处理数值
						String val = inputs.get(0).attr("value");
						if(StringUtils.isNotBlank(val)) {
//							td.text(nf.format(val));
							td.text(val);
						}
					} else {//处理回填数据，评估基准日、被评估单位
						String text = td.text();
						if (text.contains("被评估单位：")) {
							td.text("被评估单位：" + project.getEvaluateTarget());
						}
						if (text.contains("评估基准日：")) {
							td.text("评估基准日：" + baseDateStr);
						}
					}
				}
				if(isEmpty) {
					Elements inputs = td.getElementsByTag("input");
					if(inputs != null && inputs.size() > 0) {
						String val = inputs.get(0).attr("value");
//						if(StringUtils.isNotBlank(val) && !nf.format(val).equals("0.00") && !nf.format(val).equals("0.00%")) {
						if(StringUtils.isNotBlank(val) && !val.equals("0.00") && !val.equals("0.00%")) {
							isEmpty = false;
						}
					} else {
						if(StringUtils.isNotBlank(td.text().trim()) && !td.text().equals("0.00") && !td.text().equals("0.00%")) {
							isEmpty = false;
						}
					}
				}
				
				//colspan,计算实际列数
				String colspan = td.attr("colspan");
				if(StringUtils.isNotBlank(colspan)) {
					tdIndex += Integer.parseInt(colspan);
				} else {
					tdIndex++;
				}
			}
			if(isEmpty) {
				tr.remove();
			}
			//处理编号
			if(relation.equals("00020002") || relation.equals("00020088")) {//资产评估结果汇总表
				if(i == 0) {
					tds.get(0).text(keyword);
				}
				if(NumberUtil.isNumeric(tds.get(0).text())) {
					tds.get(0).text(++rowNum + "");
				}
			} else if(relation.equals("00020003")) {//资产评估结果分类汇总表
				if(NumberUtil.isNumeric(tds.get(0).text())) {
					if(StringUtils.isNotBlank(tds.get(1).text())) {
						tds.get(0).text(NumberUtil.numberToChinese(++firstNum));
						secondNum = 0;
					} else {
						tds.get(0).text((++secondNum) + "");
					}
					tds.get(1).remove();
				} else if(StringUtils.isNotBlank(tds.get(0).attr("colspan")) && Integer.parseInt(tds.get(0).attr("colspan")) > 1){
						tds.get(0).attr("colspan", (Integer.parseInt(tds.get(0).attr("colspan")) - 1) + "");
				} else {
					tds.get(1).remove();
				}
			}
		}
		
		
		
//		String unitStr = "<tr><td colspan='" + columnArr.length + "' style='text-align:right;border-top:none;border-left:none;border-right:none;border-bottom:solid #000000 1px;font-size: 13px;'>金额单位：人民币" + unit+ "</td></tr>";
//		tableEle.get(0).getElementsByTag("tr").get(0).before(unitStr);
		//重新组装字符串，加上thead、tbody，为了固定表头使用
		String newHtml = tableEle.outerHtml();
		if(StringUtils.isNotBlank(header)) {
			trs = tableEle.get(0).getElementsByTag("tr");
			if(relation.equals("00020037")) {
				trs.get(0).html("<tr><td colspan='4' style='border-top:none;border-left:none;border-right:none;border-bottom:1px solid windowtext;font-weight:400;font-size: 80%;text-align:left;'>被评估单位：" + project.getEvaluateTarget()+"</td>"
						+ "<td colspan='3' style='border-top:none;border-left:none;border-right:none;border-bottom:1px solid windowtext;font-weight:400;font-size: 80%;text-align:right;'>金额单位：人民币元</td></tr>");
			}
			newHtml="<table>";
			for(int i = 0; i < trs.size(); i++) {
				Element tr = trs.get(i);
				if(i == 0) {
					newHtml += "<thead>";
				}
				newHtml += tr.outerHtml();
				if(i == Integer.parseInt(header) - 1) {
					newHtml += "</thead><tbody>";
				}
				if(i == trs.size() - 1) {
					newHtml += "</tbody>";
				}
			}
			newHtml+="</table>";
		}
		return newHtml;
	}
	
	/**
	 * 获取明细表指定行列
	 * 值为空的行删掉
	 * @param excelStr
	 * @param rows
	 * @param columns
	 * @return
	 */
	public static String getExcelTable(String excelStr, String modelStr, QuoteModel quote,
			ProjectModel project) {
		if(StringUtils.isBlank(excelStr)) {
			return "";
		}
		String rows = quote.getRow();
		String columns = quote.getColumn();
		String header = quote.getHeader();
		String notnull = quote.getNotnull();
		String relation = quote.getRelation();
		String keyword = quote.getKeyword();
		
		Date baseDate = project.getBaseDate();
		String baseDateStr = "";
		if(baseDate != null) {
			baseDateStr = new SimpleDateFormat("yyyy年MM月dd日").format(baseDate);
		}
		
		Document doc = Jsoup.parse(excelStr);
		Elements tableEle = doc.getElementsByTag("table");
		Elements trs = tableEle.get(0).getElementsByTag("tr");
		
		Document modelDoc = Jsoup.parse(modelStr);
		Elements modelTable = modelDoc.getElementsByTag("table");
		Elements modelTrs = modelTable.get(0).getElementsByTag("tr");
		
		String[] rowsArr = rows.split(",");
		String[] columnArr = columns.split(",");
		//要获取的行
		Set<Integer> holdRows = new LinkedHashSet<Integer>();
		for(String oneRows : rowsArr) {
			String[] rowArr = oneRows.split(":");
			if(rowArr.length == 1) {
				holdRows.add(Integer.parseInt(rowArr[0]));
			} else {
				//处理增加行情况
				int endRow = Integer.parseInt(rowArr[1]);
				if(trs.size() > modelTrs.size()) {
					endRow += trs.size() - modelTrs.size();
				}
				
				for(int i = Integer.parseInt(rowArr[0]); i <= endRow; i++) {
					holdRows.add(i);
				}
			}
		}
		
		TableHtmlUtil tableHtmlUtil = new TableHtmlUtil();
		List<TR> trList = tableHtmlUtil.tableHtmlAnalyse(excelStr);
		
		List<TR> removeTRList = new ArrayList<TR>();
		for(int i = 0; i < trList.size(); i++) {
			TR tr = trList.get(i);
			//不保留的行删掉
			if(!holdRows.contains(i + 1)) {
				removeTRList.add(tr);
				continue;
			}
			//检查列值不能为空的行删掉
			List<TD> tdList = tr.getTdList();
			if(StringUtils.isNotBlank(notnull)) {
				String[] notnullArr = notnull.split(",");
				boolean isTrEmpty = true;
				for(String oneNotnull : notnullArr) {
					int notNullIndex = Integer.parseInt(oneNotnull);
					for(int j = 0; j < tdList.size(); j++) {
						TD td = tdList.get(j);
						String content = td.getContent();
						if(content.equals("合计")) {//长期股权投资使用，合计行不删，后面处理
							isTrEmpty = false;
							break;
						}
						if(td.getX() == notNullIndex - 1) {
							if(StringUtils.isNotBlank(content) && !content.equals("0.00") && !content.equals("0.00%")) {
								isTrEmpty = false;
								break;
							}
						}
					}
					if(tdList.size() < notNullIndex) {
						isTrEmpty = false;
						break;
					}
				}
				if(isTrEmpty) {
					removeTRList.add(tr);
					if(relation.equals("00020037") && i == trList.size() - 4) {// 长期股权投资
						removeTRList.add(trList.get(i - 1));
					}
					continue;
				}
			}
			
			//判断单元格是不是空的，都为空删掉行
			boolean isEmpty = true;
			List<TD> removeTdList = new ArrayList<TD>();
			for(int j = 0; j < tdList.size(); j++) {
				TD td = tdList.get(j);
				
				//不保留的列删掉
				boolean flag = false;
				for(String column : columnArr) {
					if(td.getX() == (Integer.parseInt(column) - 1)) {
						flag = true;
						break;
					}
				}
				if(!flag) {
					removeTdList.add(td);
					continue;
				}
				if(isEmpty) {
					String content = td.getContent();					
					if(StringUtils.isNotBlank(content) && !content.equals("0.00") && !content.equals("0.00%")) {
						isEmpty = false;
					}
				}
			}
			if(removeTdList != null && removeTdList.size() > 0) {
				tdList.removeAll(removeTdList);
			}
			if(isEmpty) {
				removeTRList.add(tr);
			}
		}
		if(removeTRList != null && removeTRList.size() > 0) {
			trList.removeAll(removeTRList);
		}
		StringBuilder newHtml = new StringBuilder();
		newHtml.append("<table>");
		for(int i = 0; i < trList.size(); i++) {
			TR tr = trList.get(i);
			if(StringUtils.isNotBlank(header) && i == 0) {
				newHtml.append("<thead>");
			}
			newHtml.append("<tr style='" + tr.getStyle() + "'>");
			List<TD> tdList = tr.getTdList();
			for(int j = 0; j < tdList.size(); j++) {
				TD td = tdList.get(j);
				newHtml.append(td.getHtml());
			}
			newHtml.append("</tr>");
			if(StringUtils.isNotBlank(header) && i == Integer.parseInt(header) - 1) {
				newHtml.append("</thead><tbody>");
			}
			if(StringUtils.isNotBlank(header) && i == trList.size() - 1) {
				newHtml.append("</tbody>");
			}
		}
		doc = Jsoup.parse(newHtml.toString());
		tableEle = doc.getElementsByTag("table");
		trs = tableEle.get(0).getElementsByTag("tr");
		if(relation.equals("00020037")) {
			trs.get(0).html("<tr><td colspan='4' style='border-top:none;border-left:none;border-right:none;border-bottom:1px solid windowtext;font-weight:400;font-size: 80%;text-align:left;'>被评估单位：" + project.getEvaluateTarget()+"</td>"
					+ "<td colspan='3' style='border-top:none;border-left:none;border-right:none;border-bottom:1px solid windowtext;font-weight:400;font-size: 80%;text-align:right;'>金额单位：人民币元</td></tr>");
		}
		//用于处理编号
		int rowNum = 0;//编号
		int firstNum = 0;//一级编号
		int secondNum = 0;//二级编号
		for(int i = 0; i < trs.size(); i++) {
			Element tr = trs.get(i);
			Elements tds = tr.getElementsByTag("td");
			
			for(int j = 0, tdIndex = 0; j < tds.size(); j++) {
				Element td = tds.get(j);
				Elements inputs = td.getElementsByTag("input");
				if(inputs != null && inputs.size() > 0) { //处理数值
					String val = inputs.get(0).attr("value");
					if(StringUtils.isNotBlank(val)) {
//						td.text(nf.format(val));
						td.text(val);
					}
				} else {//处理回填数据，评估基准日、被评估单位
					String text = td.text();
					if (text.contains("被评估单位：")) {
						td.text("被评估单位：" + project.getEvaluateTarget());
					}
					if (text.contains("评估基准日：")) {
						td.text("评估基准日：" + baseDateStr);
					}
				}
			}
			//处理编号
			if(relation.contains("00020002") || relation.contains("00020088") || relation.contains("00020102")) {//资产评估结果汇总表
				if(i == 0) {
					tds.get(0).text(tds.get(0).text().replace(" ", ""));
				}
				if(NumberUtil.isNumeric(tds.get(0).text())) {
					tds.get(0).text(++rowNum + "");
				}
			} else if(relation.equals("00020003")) {//资产评估结果分类汇总表
				if(NumberUtil.isNumeric(tds.get(0).text())) {
					if(StringUtils.isNotBlank(tds.get(1).text())) {
						tds.get(0).text(NumberUtil.numberToChinese(++firstNum));
						secondNum = 0;
					} else {
						tds.get(0).text((++secondNum) + "");
					}
					tds.get(1).remove();
				} else if(StringUtils.isNotBlank(tds.get(0).attr("colspan")) && Integer.parseInt(tds.get(0).attr("colspan")) > 1){
						tds.get(0).attr("colspan", (Integer.parseInt(tds.get(0).attr("colspan")) - 1) + "");
				} else {
					tds.get(1).remove();
				}
			}
		}
		return tableEle.outerHtml();
	}
}